﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Web.UI.HtmlControls;
using System.Text;
using ClosedXML.Excel;
public partial class Main_Reports_Inventory_StockReport : System.Web.UI.Page
{
    Datautility DU = new Datautility();
    DataTable Dt = new DataTable();
    int TotalOpeningBalanceQty = 0; double TotalOpeningBalanceValue = 0;
    int TotalPurchaseQty = 0; double TotalPurchaseValue = 0;
    int TotalIssueQty = 0; double TotalIssuevalue = 0;
    int TotalReturnQty = 0; double TotalReturnValue = 0;
    int TotalClossingQty = 0; double TotalClossingValue = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //txtfromdate.Text = System.DateTime.Now.ToString(string.Format("dd/MM/yyyy"));           
            //GetData();            
        }
    }
    //*********************************************//
    //************Please Don't Delete this Code**********//
    //*************Commented By Harish Kumar******************//
    //protected void GetData()
    //{
    //    try
    //    {
    //        string todate = "";
    //        string fromdate = "";
    //        DataTable dtstockDetail = DU.getdatausingprocedure("Usp_IVR_GetStockDetails");
    //        Dt.Columns.Add("ITMID", typeof(Int32));
    //        Dt.Columns.Add("ItemCode", typeof(string));
    //        Dt.Columns.Add("ItemName", typeof(string));
    //        Dt.Columns.Add("BinNo", typeof(string));
    //        Dt.Columns.Add("LocationID", typeof(string));
    //        Dt.Columns.Add("MPN", typeof(string));
    //        Dt.Columns.Add("ITEMGROUP", typeof(string));
    //        Dt.Columns.Add("ALTITEMCODE", typeof(string));
    //        Dt.Columns.Add("ItemAddlDesc", typeof(string));
    //        Dt.Columns.Add("Project", typeof(string));
    //        Dt.Columns.Add("LeasedType", typeof(string));
    //        Dt.Columns.Add("PurchaseQty", typeof(string));
    //        Dt.Columns.Add("PurchaseValue", typeof(string));
    //        Dt.Columns.Add("IssueQty", typeof(string));
    //        Dt.Columns.Add("IssueValue", typeof(string));
    //        Dt.Columns.Add("ReturnQty", typeof(string));
    //        Dt.Columns.Add("ReturnValue", typeof(string));
    //        Dt.Columns.Add("OpeningBalQty", typeof(int));
    //        Dt.Columns.Add("OpeningBalValue", typeof(string));
    //        Dt.Columns.Add("ClosingQty", typeof(string));
    //        Dt.Columns.Add("ClosingValue", typeof(string));
    //        Dt.Columns.Add("LandedCost", typeof(decimal));            
    //        double TempOpeningValue = 0; double TempPurValue = 0; string IssueItem = ""; string ReturnItem = "";
    //        double TempPurQty = 0; double TempIssueQty = 0; double TempPurIssueValue = 0;
    //        double tempRetrunQty = 0; double TempRetrunValue = 0; double TempBalanceQty = 0;
    //        double Actualpurchse = 0; double actualissue = 0; double actualreturn = 0;
    //        double ActualPurValue = 0; double ActualPurIssueValue = 0; double ActualRetrunValue = 0;
    //        string MPNNo = ""; string Openingstock = ""; string Purchase = "";
    //        if (dtstockDetail.Rows.Count > 0)
    //        {
    //            if (txttodate.Text != "")
    //            {
    //                todate = Convert.ToDateTime(txttodate.Text).ToString(string.Format("yyyy/MM/dd"));
    //            }
    //            else
    //            {
    //                todate = System.DateTime.Now.ToString("yyyy/MM/dd");
    //            }
    //            if (txtfromdate.Text != "")
    //            {
    //                fromdate = Convert.ToDateTime(txtfromdate.Text).ToString(string.Format("yyyy-MM-dd"));
    //            }
    //            lblRecordNo.Text = "To Date" + todate;
    //            lblperiod.Text = "From Date" + fromdate;
    //            for (int i = 0; i < dtstockDetail.Rows.Count; i++)
    //            {
    //                DataRow DR = Dt.NewRow();                                      
    //                DR["ItemCode"] = dtstockDetail.Rows[i]["ItemCode"];
    //                DR["ItemName"] = dtstockDetail.Rows[i]["ItemName"];
    //                DR["BinNo"] = dtstockDetail.Rows[i]["BinNo"];
    //                DR["ALTITEMCODE"] = dtstockDetail.Rows[i]["ALTITEMCODE"];
    //                if (dtstockDetail.Rows[i]["LocationID"].ToString() == "")
    //                {
    //                    DR["LocationID"] = "-";
    //                }
    //                else
    //                {
    //                    DR["LocationID"] = dtstockDetail.Rows[i]["LocationID"];
    //                }
    //                string MpnSql = "select MPNCODE from IVR_ITEM_MPN where ITEMID=" + dtstockDetail.Rows[i]["ITMID"].ToString();
    //                DataTable DTMPN = DU.getdata(MpnSql);
    //                if (DTMPN.Rows.Count > 0)
    //                {
    //                    for (int j = 0; j < DTMPN.Rows.Count; j++)
    //                    {
    //                        if (DTMPN.Rows[j]["MPNCODE"].ToString() != "")
    //                        {
    //                            MPNNo += DTMPN.Rows[j]["MPNCODE"].ToString() + ",";
    //                        }
    //                    }
    //                    if (MPNNo.Length > 0)
    //                    {
    //                        DR["MPN"] = MPNNo.Substring(0, MPNNo.Length - 1).Trim();
    //                    }
    //                }
    //                else
    //                {
    //                    DR["MPN"] = "-";
    //                }
    //                DR["ITEMGROUP"] = dtstockDetail.Rows[i]["ITEMGROUP"];
    //                if (dtstockDetail.Rows[i]["ItemAddlDesc"].ToString() == "0")
    //                {
    //                    DR["ItemAddlDesc"] = "-";
    //                }
    //                else
    //                {
    //                    DR["ItemAddlDesc"] = dtstockDetail.Rows[i]["ItemAddlDesc"];
    //                }
    //                DR["Project"] = dtstockDetail.Rows[i]["Project"];
    //                DR["LeasedType"] = dtstockDetail.Rows[i]["LeasedType"];

    //                //Query.... For Report                    
    //                Openingstock = "select ISNULL(CONVERT(decimal(18,0),Quantity),0) - ISNULL(CONVERT(decimal(18,0),IssueQty ),0) + ISNULL(CONVERT(decimal(18,0),RetrunQty ),0) as BalanceQty,ISNULL(RetrunQty,0) as RetrunQty,ISNULL(Quantity,0) as Quantity,ISNULL(IssueQty,0) as IssueQty,AMOUNT,RATE from IVR_ITEMOPENBAL where ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and OPDATE = '2014-04-01'";
    //                // for finding purchase opening ....                    
    //                Purchase = "select ISNULL(CONVERT(decimal(18,0),IVR_TRAN_ITEM.ITMQTY),0)- ISNULL(CONVERT(decimal(18,0),IVR_TRAN_ITEM.IssueQty),0) as BalanceQTY, IVR_TRAN_ITEM.ITMQTY as ITMQTY,IVR_TRAN_ITEM.IssueQty as IssueQty,IVR_TRAN_ITEM.RATE as RATE,TOTALINVENTCOST, GROSSAMT from IVR_TRAN_ITEM inner join IVR_TRAN_MAIN on IVR_TRAN_MAIN.TRNID=IVR_TRAN_ITEM.TRNID where IVR_TRAN_ITEM.ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and IVR_TRAN_ITEM.EntryType='PUR' and IVR_TRAN_MAIN.PurchaseRecDate < '" + fromdate + "'";
    //                // for getting issue opeing...                    
    //                IssueItem = "select SUM(ISSUEQTY)as ISSUEQTY,sum(ISSUEVALUE)as ISSUEVALUE from IVR_COMMONITEM_ISSUERETURN where ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and ISSUEDATE < '" + fromdate + "' and ENTRYTYPE <>'RET'";
    //                // for getting return opening..                    
    //                ReturnItem = "select SUM(RETURNQTY)as RETURNQTY,sum(RETURNVALUE)as RETURNVALUE from IVR_COMMONITEM_ISSUERETURN where ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and RETURNDATE < '" + fromdate + "' and ENTRYTYPE = 'RET'";

    //                //Data table for the Opening...
    //                DataTable DtOpening = DU.getdata(Openingstock);
    //                DataTable DTPurchase = DU.getdata(Purchase);
    //                DataTable DTIssueItem = DU.getdata(IssueItem);
    //                DataTable DTReturnItem = DU.getdata(ReturnItem);
    //                //Get The value from the Opening Balance Qty AN dValue from the IVR_ITEMOPENBAL
    //                if (DtOpening.Rows.Count > 0)
    //                {
    //                    TempBalanceQty = Convert.ToDouble(DtOpening.Rows[0]["Quantity"].ToString());
    //                    TempOpeningValue = Convert.ToDouble(DtOpening.Rows[0]["AMOUNT"].ToString()); //* Convert.ToDouble(DtOpening.Rows[0]["RATE"].ToString());
    //                }
    //                //From the Purchase Receive
    //                if (DTPurchase.Rows.Count > 0)
    //                {
    //                    for (int j = 0; j < DTPurchase.Rows.Count; j++)
    //                    {
    //                        TempPurValue += Convert.ToDouble(DTPurchase.Rows[j]["GROSSAMT"].ToString());
    //                        TempPurQty += Convert.ToDouble(DTPurchase.Rows[j]["ITMQTY"].ToString());
    //                    }
    //                }
    //                //Issue Qty And Value.
    //                if (DTIssueItem.Rows.Count > 0)
    //                {
    //                    if (DTIssueItem.Rows[0]["ISSUEQTY"].ToString() != "")
    //                    {
    //                        TempIssueQty = Convert.ToDouble(DTIssueItem.Rows[0]["ISSUEQTY"].ToString());
    //                        TempPurIssueValue = Convert.ToDouble(DTIssueItem.Rows[0]["ISSUEVALUE"].ToString());
    //                    }
    //                }
    //                //Retrun Item ANd Value.
    //                if (DTReturnItem.Rows.Count > 0)
    //                {
    //                    if (DTReturnItem.Rows[0]["RETURNQTY"].ToString() != "")
    //                    {
    //                        tempRetrunQty = Convert.ToDouble(DTReturnItem.Rows[0]["RETURNQTY"].ToString());
    //                        TempRetrunValue = Convert.ToDouble(DTReturnItem.Rows[0]["RETURNVALUE"].ToString());
    //                    }
    //                }
    //                //Binding Opening Values And Qty of the  Items
    //                double actualopeningqty = (TempPurQty + tempRetrunQty + TempBalanceQty - TempIssueQty);
    //                double ActualOpeningvalue = (TempOpeningValue + TempPurValue + TempRetrunValue - TempPurIssueValue);

    //                //Actual purchase between dates..
    //                string Purchaseactual = "select ISNULL(CONVERT(decimal(18,0),IVR_TRAN_ITEM.ITMQTY),0)- ISNULL(CONVERT(decimal(18,0),IVR_TRAN_ITEM.IssueQty),0) as BalanceQTY, IVR_TRAN_ITEM.ITMQTY as ITMQTY,IVR_TRAN_ITEM.IssueQty as IssueQty,IVR_TRAN_ITEM.RATE as RATE,TOTALINVENTCOST, GROSSAMT from IVR_TRAN_ITEM inner join IVR_TRAN_MAIN on IVR_TRAN_MAIN.TRNID=IVR_TRAN_ITEM.TRNID where IVR_TRAN_ITEM.ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and IVR_TRAN_ITEM.EntryType='PUR' and IVR_TRAN_MAIN.PurchaseRecDate Between '" + fromdate + "' and '" + todate + "'";
    //                //Actual Issue Between Dates.....
    //                string IssueItemactual = "select SUM(ISSUEQTY)as ISSUEQTY,sum(ISSUEVALUE)as ISSUEVALUE from IVR_COMMONITEM_ISSUERETURN where ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and ISSUEDATE between '" + fromdate + "' and '" + todate + "' and ENTRYTYPE <>'RET'";
    //                //Actual Retrun Between Dates....
    //                string ReturnItemactual = "select SUM(RETURNQTY)as RETURNQTY,sum(RETURNVALUE)as RETURNVALUE from IVR_COMMONITEM_ISSUERETURN where ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and RETURNDATE between '" + fromdate + "' and '" + todate + "' and ENTRYTYPE = 'RET'";

    //                //Datatable fill with following DataTable
    //                DataTable dtactualpurchse = DU.getdata(Purchaseactual);
    //                DataTable dtactualissue = DU.getdata(IssueItemactual);
    //                DataTable dtactualreturn = DU.getdata(ReturnItemactual);

    //                //For the Actual Purchase of the Item.
    //                if (dtactualpurchse.Rows.Count > 0)
    //                {
    //                    for (int i1 = 0; i1 < dtactualpurchse.Rows.Count; i1++)
    //                    {
    //                        Actualpurchse += Convert.ToDouble(dtactualpurchse.Rows[i1]["ITMQTY"].ToString());
    //                        ActualPurValue += Convert.ToDouble(dtactualpurchse.Rows[i1]["GROSSAMT"].ToString());
    //                    }
    //                }
    //                //For the Actual Retrun Of the Item.
    //                if (dtactualreturn.Rows.Count > 0)
    //                {
    //                    if (dtactualreturn.Rows[0]["RETURNQTY"].ToString() != "")
    //                    {
    //                        actualreturn = Convert.ToDouble(dtactualreturn.Rows[0]["RETURNQTY"].ToString());
    //                        ActualRetrunValue = Convert.ToDouble(dtactualreturn.Rows[0]["RETURNVALUE"].ToString());
    //                    }
    //                }
    //                //For the Actual Issue of the Item.
    //                if (dtactualissue.Rows.Count > 0)
    //                {
    //                    if (dtactualissue.Rows[0]["ISSUEQTY"].ToString() != "")
    //                    {
    //                        actualissue = Convert.ToDouble(dtactualissue.Rows[0]["ISSUEQTY"].ToString());
    //                        ActualPurIssueValue = Convert.ToDouble(dtactualissue.Rows[0]["ISSUEVALUE"].ToString());
    //                    }
    //                }

    //                //For the Binding Of the DataRow.
    //                DR["OpeningBalQty"] = actualopeningqty; //From the Opening Table of Item
    //                DR["OpeningBalValue"] = ActualOpeningvalue.ToString("N2");// From the Opening value of the Item.

    //                DR["PurchaseQty"] = Actualpurchse; //From the Purchase Receive of the Item
    //                DR["PurchaseValue"] = ActualPurValue.ToString("N2");// From the Purchase Reveive value of the Item

    //                DR["IssueQty"] = actualissue; //Issue Qty come from the Issue table of the Item
    //                DR["IssueValue"] = ActualPurIssueValue.ToString("N2"); //After the calculation of the Opening Value as well as the Purchase Value.

    //                DR["ReturnQty"] = actualreturn;
    //                DR["ReturnValue"] = ActualRetrunValue.ToString("N2");

    //                DR["ClosingQty"] = (actualopeningqty + Actualpurchse + actualreturn) - actualissue;// from the Opening Qty + Purchase Qty - Issue Qty + Item Return  OF the Item.
    //                DR["ClosingValue"] = ((ActualOpeningvalue + ActualPurValue + ActualRetrunValue) - ActualPurIssueValue).ToString("N2");//from the Opening Value + Purchase Value - Issue Value + Item Return VAlue  OF the Item.

    //                /*Commented by the Krishna Sir.*/

    //                //DR["LandedCost"] = TempLandedCost + TempOpeningValue;
    //                //if ((TempBalanceQty + TempPurchaseQty + TempClosingQty + TempPurQtyclosing) > 500)
    //                //{
    //                //    DR["InventoryClass"] = "A";
    //                //}
    //                //else if ((TempBalanceQty + TempPurchaseQty + TempClosingQty + TempPurQtyclosing) >= 100 || (TempBalanceQty + TempPurchaseQty + TempClosingQty + TempPurQtyclosing) <= 499)
    //                //{
    //                //    DR["InventoryClass"] = "B";
    //                //}
    //                //else if ((TempBalanceQty + TempPurchaseQty + TempClosingQty + TempPurQtyclosing) <= 99)
    //                //{                    
    //                //    DR["InventoryClass"] = "C";
    //                //}

    //                Dt.Rows.Add(DR);
    //                //Empty Data.
    //                #region Empty the Variable
    //                MPNNo = "";
    //                TempOpeningValue = 0; TempPurValue = 0; TempIssueQty = 0;
    //                TempBalanceQty = 0; TempPurQty = 0; TempPurIssueValue = 0; tempRetrunQty = 0; TempRetrunValue = 0;
    //                actualreturn = 0; Actualpurchse = 0; actualopeningqty = 0; actualissue = 0;
    //                ActualPurValue = 0; ActualPurIssueValue = 0; ActualRetrunValue = 0;
    //                DtOpening.Clear();
    //                DTPurchase.Clear();
    //                #endregion
    //            }
    //            grdStockReport.DataSource = Dt;                
    //            grdStockReport.DataBind();
    //            lblRecordNo.Text = grdStockReport.Rows.Count.ToString() + " Record(s)";
    //            lblperiod.Text = "Period :" + Convert.ToDateTime(fromdate).ToString(string.Format("dd-MM-yyyy")) + " to " + Convert.ToDateTime(todate).ToString(string.Format("dd-MM-yyyy"));
    //        }
    //        fromdate = ""; todate = "";            
    //    }
    //    catch (Exception ex)
    //    {
    //        lblvalue.Visible = true;
    //        lblvalue.Text = ex.Message;
    //    }
    //}
    //***************************************************//
    //***************************************************//

    protected void GetData()
    {
        if (txtfromdate.Text != "" && txttodate.Text != "")
        {
            lblperiod.Text = "Period :" + txtfromdate.Text + " to " + txttodate.Text;
            SqlParameter[] StockParam = { 
                                            new SqlParameter("@fromdate",Convert.ToDateTime(txtfromdate.Text).ToString(string.Format("yyyy/MM/dd"))),
                                            new SqlParameter("@todate",Convert.ToDateTime(txttodate.Text).ToString(string.Format("yyyy/MM/dd")))
                                        };
            DataTable Dt = DU.getdatausingprocedure("IVR_RPT_STOCK_REPORT",StockParam);
            if(Dt.Rows.Count>0)
            {
                grdStockReport.DataSource=Dt;
                grdStockReport.Columns[11].FooterText = Dt.AsEnumerable().Select(x => x.Field<decimal>("OpeningQty")).Sum().ToString("N0");
                grdStockReport.Columns[12].FooterText = Dt.AsEnumerable().Select(x => x.Field<decimal>("OpeningValue")).Sum().ToString("N2");
                grdStockReport.Columns[13].FooterText = Dt.AsEnumerable().Select(x => x.Field<decimal>("PurchaseQty")).Sum().ToString("N0");
                grdStockReport.Columns[14].FooterText = Dt.AsEnumerable().Select(x => x.Field<decimal>("PurchaseValue")).Sum().ToString("N2");
                grdStockReport.Columns[15].FooterText = Dt.AsEnumerable().Select(x => x.Field<int>("IssueQTY")).Sum().ToString("N0");
                grdStockReport.Columns[16].FooterText = Dt.AsEnumerable().Select(x => x.Field<decimal>("IssueValue")).Sum().ToString("N2");
                grdStockReport.Columns[17].FooterText = Dt.AsEnumerable().Select(x => x.Field<int>("ReturnQty")).Sum().ToString("N0");
                grdStockReport.Columns[18].FooterText = Dt.AsEnumerable().Select(x => x.Field<decimal>("ReturnValue")).Sum().ToString("N2");
                grdStockReport.Columns[19].FooterText = Dt.AsEnumerable().Select(x => x.Field<decimal>("ClosingQty")).Sum().ToString("N0");
                grdStockReport.Columns[20].FooterText = Dt.AsEnumerable().Select(x => x.Field<decimal>("ClosingValue")).Sum().ToString("N2");
                grdStockReport.DataBind();
                lblRecordNo.Text = grdStockReport.Rows.Count.ToString() + " Record(s)";
                lblperiod.Text = "Period :" + txtfromdate.Text + " to " + txttodate.Text;
            }
        }
    }
    protected void grdStockReport_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grdStockReport.PageIndex = e.NewPageIndex;
        GetData();
    }
    protected void btngetreport_Click(object sender, EventArgs e)
    {
        try
        {
            GetData();
        }
        catch (Exception ex)
        {
            lblvalue.Visible = true;
            lblvalue.Text = ex.Message;
        }
    }
    protected void btnReport_Click(object sender, EventArgs e)
    {
        grdStockReport.Visible = true;
        GetData();
        string Filter = "";
        if (ddlColumnname.SelectedValue == "1")//for Item Code
        {
            Filter = string.Format(CultureInfo.InvariantCulture, "ItemCode like'%" + txtValue.Text + "%'");           
            grdStockReport.AllowPaging = false;
        }
        else if (ddlColumnname.SelectedValue == "2")//for Item name
        {
            Filter = string.Format(CultureInfo.InvariantCulture, "ItemName Like'%" + txtValue.Text + "%'");           
            grdStockReport.AllowPaging = false;
        }
        else if (ddlColumnname.SelectedValue == "3")//For Project
        {
            Filter = string.Format(CultureInfo.InvariantCulture, "Project Like'%" + txtValue.Text + "%'");            
            grdStockReport.AllowPaging = false;
        }
        else if (ddlColumnname.SelectedValue == "4")//For BinNo
        {
            Filter = string.Format(CultureInfo.InvariantCulture, "BinNo Like'%" + txtValue.Text + "%'");                  
            grdStockReport.AllowPaging = false;
        }
        else if (ddlColumnname.SelectedValue == "5")//For MPN
        {
            Filter = string.Format(CultureInfo.InvariantCulture, "MPN Like'%" + txtValue.Text + "%'");            
            grdStockReport.AllowPaging = false;
        }
        else if (ddlColumnname.SelectedValue == "6")//Item Group
        {
            Filter = string.Format(CultureInfo.InvariantCulture, "ItemGroup='" + txtValue.Text + "'");            
            grdStockReport.AllowPaging = false;
        }
        else if (ddlColumnname.SelectedValue == "7") //For the Inventory Class
        {
            Filter = string.Format(CultureInfo.InvariantCulture, "InventoryClass='" + txtValue.Text + "'");
            grdStockReport.AllowPaging = false;
        }        
        else
        {
            GetData();
        }
        DataRow[] Dr = Dt.Select(Filter);
        DataTable Tempdt = new DataTable();
        Tempdt = Dt.Clone();
        foreach (DataRow drNew in Dr)
        {
            Tempdt.ImportRow(drNew);
        }
        grdStockReport.DataSource = Tempdt;
        grdStockReport.DataBind();
    }
    protected void grdStockReport_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        //if (e.Row.RowType == DataControlRowType.DataRow)
        //{
        //    //Label LblOpeningBalanaceQty=(Label) e.Row.FindControl("lblopeningQty");
        //    // OpeningBalanceQty +=Convert.ToInt32(LblOpeningBalanaceQty.Text);
        //    TotalOpeningBalanceQty += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "OpeningBalQty"));
        //    TotalOpeningBalanceValue += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "OpeningBalValue"));
        //    TotalPurchaseQty += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "PurchaseQty"));
        //    TotalPurchaseValue += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "PurchaseValue"));
        //    TotalIssueQty += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "IssueQty"));
        //    TotalIssuevalue += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "IssueValue"));
        //    TotalReturnQty += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "ReturnQty"));
        //    TotalReturnValue += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "ReturnValue"));
        //    TotalClossingQty += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "ClosingQty"));
        //    TotalClossingValue += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "ClosingValue"));
        //}
        //if (e.Row.RowType == DataControlRowType.Footer)
        //{
        //    Label lblTotalQty = (Label)e.Row.FindControl("lblOpeningtotal");
        //    lblTotalQty.Text = TotalOpeningBalanceQty.ToString();
        //    Label lblTotalValue = (Label)e.Row.FindControl("lblOpeningTotalValue");
        //    lblTotalValue.Text = TotalOpeningBalanceValue.ToString();

        //    Label LblTotalPurchaseQty = (Label)e.Row.FindControl("lblPurchaseTotalQty");
        //    LblTotalPurchaseQty.Text = TotalPurchaseQty.ToString();
        //    Label LblTotalPurchaseValue = (Label)e.Row.FindControl("lblPurchaseTotalValue");
        //    LblTotalPurchaseValue.Text = TotalPurchaseValue.ToString();

        //    Label LblTotalIssueQty = (Label)e.Row.FindControl("lblTotalIssueQty");
        //    LblTotalIssueQty.Text = TotalIssueQty.ToString();
        //    Label LblTotalIssueValue = (Label)e.Row.FindControl("lblTotalIssueValue");
        //    LblTotalIssueValue.Text = TotalIssuevalue.ToString();

        //    Label LblTotalReturnQty = (Label)e.Row.FindControl("lblTotalReturnQty");
        //    LblTotalReturnQty.Text = TotalReturnQty.ToString();
        //    Label LblTotalReturnValue = (Label)e.Row.FindControl("lblTotalReturnValue");
        //    LblTotalReturnValue.Text = TotalReturnValue.ToString();

        //    Label LblTotalClossingQty = (Label)e.Row.FindControl("lbltotalClossingQty");
        //    LblTotalClossingQty.Text = TotalClossingQty.ToString();
        //    Label LblTotalClossingValue = (Label)e.Row.FindControl("lbltotalClossingValue");
        //    LblTotalClossingValue.Text = TotalClossingValue.ToString();
        //}
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        //base.VerifyRenderingInServerForm(control);
    }
    protected void ImgBtnExcel_Click(object sender, ImageClickEventArgs e)
    {
        //for Exporting In Excel file.
        try
        {
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Stock_Report.xls"));
            Response.ContentType = "application/ms-excel";
            grdStockReport.ShowFooter = false;
            grdStockReport.AllowPaging = false;
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            grdStockReport.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();
            //ExportToExcel();
        }
        catch (Exception ex)
        {
        }
    }
    protected void ExportToExcel()
    {
        grdStockReport.AllowPaging = false;
        DataTable dttemp = new DataTable("GridViewData");
        foreach (TableCell cell in grdStockReport.HeaderRow.Cells)
        {
            dttemp.Columns.Add(cell.Text);
        }        
        for (int i = 0; i < grdStockReport.Rows.Count; i++)
        {
            dttemp.Rows.Add();
            Label lblitemcode = (Label)grdStockReport.Rows[i].FindControl("lblitemcode");
            Label lblbinno = (Label)grdStockReport.Rows[i].FindControl("lblbinno");
            Label lblocationId = (Label)grdStockReport.Rows[i].FindControl("lbllocationid");
            Label lblmpn = (Label)grdStockReport.Rows[i].FindControl("lblmpn");
            Label lblitemGroup = (Label)grdStockReport.Rows[i].FindControl("lblitemgroup");
            Label lbladldesc = (Label)grdStockReport.Rows[i].FindControl("lbladdldesc");
            Label lblaltcode = (Label)grdStockReport.Rows[i].FindControl("lblaltitemcode");
            Label lblProject = (Label)grdStockReport.Rows[i].FindControl("lblproject");
            Label lblleasedtype = (Label)grdStockReport.Rows[i].FindControl("lblLeasedType");
            Label lblopeningqty = (Label)grdStockReport.Rows[i].FindControl("lblopeningQty");
            Label lblopeningvalue = (Label)grdStockReport.Rows[i].FindControl("lblopeningvalue");
            Label lblpurchaseqty = (Label)grdStockReport.Rows[i].FindControl("lblPurchaseQty");
            Label lblpurchasevalue = (Label)grdStockReport.Rows[i].FindControl("lblPurchaseValue");
            Label lblissueqty = (Label)grdStockReport.Rows[i].FindControl("lblIssueqty");
            Label lblissuevalue = (Label)grdStockReport.Rows[i].FindControl("lblissueValue");
            Label lblreturnqty = (Label)grdStockReport.Rows[i].FindControl("lblReturnqty");
            Label lblreturnvalue = (Label)grdStockReport.Rows[i].FindControl("lblReturnValue");
            Label lblclosingqty = (Label)grdStockReport.Rows[i].FindControl("lblclossingqty");
            Label lblclosingvalue = (Label)grdStockReport.Rows[i].FindControl("lblclossingvalue");
            for (int j = 0; j < grdStockReport.Columns.Count; j++)
            {
                if (j == 0) { dttemp.Rows[i][j] = (i+1).ToString(); }                
                if (j == 1) { dttemp.Rows[i][j] = lblitemcode.Text;}
                if (j == 2) { dttemp.Rows[i][j] = lblbinno.Text; }
                if (j == 3) { dttemp.Rows[i][j] = lblocationId.Text; }
                if (j == 4) { dttemp.Rows[i][j] = lblmpn.Text; }
                if (j == 5) { dttemp.Rows[i][j] = lblitemGroup.Text; }
                if (j == 6) { dttemp.Rows[i][j] = lbladldesc.Text; }
                if (j == 7) { dttemp.Rows[i][j] = lblaltcode.Text; }
                if (j == 8) { dttemp.Rows[i][j] = lblProject.Text; }
                if (j == 9) { dttemp.Rows[i][j] = lblleasedtype.Text; }
                if (j == 10) { dttemp.Rows[i][j] = lblopeningqty.Text; }
                if (j == 11) { dttemp.Rows[i][j] = lblopeningvalue.Text; }
                if (j == 12) { dttemp.Rows[i][j] = lblpurchaseqty.Text; }
                if (j == 13) { dttemp.Rows[i][j] = lblpurchasevalue.Text; }
                if (j == 14) { dttemp.Rows[i][j] = lblissueqty.Text; }
                if (j == 15) { dttemp.Rows[i][j] = lblissuevalue.Text; }
                if (j == 16) { dttemp.Rows[i][j] = lblreturnqty.Text; }
                if (j == 17) { dttemp.Rows[i][j] = lblreturnvalue.Text; }
                if (j == 18) { dttemp.Rows[i][j] = lblclosingqty.Text; }
                if (j == 19) { dttemp.Rows[i][j] = lblclosingvalue.Text; }                
            }
        }        
        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(dttemp);
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=StockReport.xlsx");
            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }
}